package com.apes.scm.account.creditLine.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.jpa.Connector;
import com.apes.framework.query.ApesDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.stream.Collectors;

/**
 * @program: apes-server
 * @description
 * @author: czs
 * @create: 2021-05-26 15:13
 **/
@Component
public class CreditLineDao extends ApesDao {
    @Autowired
    Connector connector;

    public JSONArray findAllCreditLine(JSONObject param) {

        StringBuffer sf1 = new StringBuffer();
        sf1.append(" SELECT * FROM MAS_PARTY_ROLE WHERE PARTY_ID=(SELECT PARTY_ID FROM MAS_PARTY_ROLE WHERE ID= (SELECT COMPANY_ID FROM MAS_DEPT WHERE ID ='"+param.getString("dept_id")+"'))");
        JSONArray party = toJSONArray(find(sf1.toString(), param));
        JSONObject customerRole = (JSONObject) party.stream().filter(item -> {
            JSONObject itemJSON = (JSONObject) item;
            return "Customer".equals(itemJSON.getString("ROLE_ID"));
        }).collect(Collectors.toList()).get(0);
        StringBuffer sf = new StringBuffer();
        sf.append(" SELECT COMPANY NAME,SUM(AMOUNT)AVAILABLE,COMPANY_ID ID,SUM(CURRENT_UNSETTLED)USED ");
        sf.append(" FROM (SELECT B.ID,B.NAME,A.AMOUNT,0 CURRENT_UNSETTLED ,A.PARTNER_ID,A.DEPT_ID,A.COMPANY_ID,");
        sf.append(" (SELECT NAME FROM MAS_DEPT  WHERE A.DEPT_ID = ID)DEPT,");
        sf.append(" (SELECT F.NAME FROM MAS_PARTY_ROLE D, MAS_PARTY F WHERE A.COMPANY_ID = D.ID AND D.PARTY_ID = F.ID) COMPANY,");
        sf.append(" (SELECT F.NAME FROM MAS_PARTY_ROLE D, MAS_PARTY F WHERE A.PARTNER_ID = D.ID AND D.PARTY_ID = F.ID) PARTNER_NAME ");
        sf.append(" FROM  ACC_LEDGER A ,ACC_LEDGER_ACCOUNT B WHERE A.LEDGER_ACCOUNT_ID = B.ID  AND B.ID IN ('prepayment_unsettled','credit') AND A.PARTNER_ID = ");
        sf.append("  '"+customerRole.getString("ID")+ "'");
        sf.append(" UNION ALL ");
        sf.append("  SELECT B.ID,B.NAME,0 AMOUT,A.AMOUNT CURRENT_UNSETTLED,A.PARTNER_ID,A.DEPT_ID,A.COMPANY_ID,");
        sf.append(" (SELECT NAME FROM MAS_DEPT  WHERE A.DEPT_ID = ID)DEPT,");
        sf.append(" (SELECT F.NAME FROM MAS_PARTY_ROLE D, MAS_PARTY F WHERE A.COMPANY_ID = D.ID AND D.PARTY_ID = F.ID) COMPANY,");
        sf.append(" (SELECT F.NAME FROM MAS_PARTY_ROLE D, MAS_PARTY F WHERE A.PARTNER_ID = D.ID AND D.PARTY_ID = F.ID) PARTNER_NAME");
        sf.append(" FROM  ACC_LEDGER A ,ACC_LEDGER_ACCOUNT B WHERE A.LEDGER_ACCOUNT_ID = B.ID AND B.ID = 'current_unsettled' AND A.PARTNER_ID = '"+customerRole.getString("ID")+"' ) A ");
        sf.append(" WHERE 1=1 GROUP BY PARTNER_ID,PARTNER_NAME,COMPANY,COMPANY_ID ");
        return toJSONArray(find(sf.toString(), param));
    }

    /**
     * 根據公司和客戶获取信用额度和已用额度
     * @param param {partnerId:K1001,companyId:1001}
     * @return
     */
    public JSONArray findCreditLineByCompanyAndPartner(JSONObject param) {
        StringBuffer sf = new StringBuffer();
        sf.append("SELECT COMPANY_ID, COMPANY COMPANY_NAME, PARTNER_ID, PARTNER_NAME, COMPANY,\n" +
                "       SUM(AMOUNT) CREDITS_LINE, SUM(CURRENT_UNSETTLED) CURRENT_UNSETTLED,\n" +
                "       SUM(CURRENT_FREEZE) CURRENT_FREEZE,\n" +
                "       SUM(INVOICE_UNSETTLED) INVOICE_UNSETTLED,\n" +
                "       SUM(INVOICE_FREEZE) INVOICE_FREEZE,\n" +
                "       sum(period) SURPLUS_QUOTA," +
                " Nvl(round(sum(period) - (sysdate - TRUNC((SELECT min(d.create_date) FROM acc_invoice d, ACC_INVOICE_ITEM i where d.company_id = a.COMPANY_ID\n" +
                "                            and d.id = i.invoice_id and ((i.SETTLED_QTY + i.freeze_Qty) * i.PRICE_UNIT) != i.PRICE_SUBTOTAL and d.partner_id = a.PARTNER_ID)))), sum(period)) SURPLUS_DATE" +
                "  FROM (SELECT B.ID, B.NAME, A.PARTNER_ID, A.DEPT_ID, A.COMPANY_ID,\n" +
                "               (CASE WHEN B.ID IN ('prepayment_unsettled', 'credit') THEN A.AMOUNT ELSE 0 END) AMOUNT, \n" +
                "               (CASE WHEN B.ID = 'current_unsettled' THEN A.AMOUNT ELSE 0 END) CURRENT_UNSETTLED, \n" +
                "               (CASE WHEN B.ID = 'current_freeze' THEN A.AMOUNT ELSE 0 END) CURRENT_FREEZE,\n" +
                "               (CASE WHEN B.ID = 'invoice_unsettled' THEN A.AMOUNT ELSE 0 END) INVOICE_UNSETTLED, \n" +
                "               (CASE WHEN B.ID = 'invoice_freeze' THEN A.AMOUNT ELSE 0 END) INVOICE_FREEZE,\n" +
                "               (CASE WHEN B.ID = 'period' THEN A.AMOUNT ELSE 0 END) period,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE A.DEPT_ID = ID) DEPT,\n" +
                "               (SELECT F.NAME FROM MAS_PARTY_ROLE D, MAS_PARTY F WHERE A.COMPANY_ID = D.ID AND D.PARTY_ID = F.ID) COMPANY,\n" +
                "               (SELECT F.NAME FROM MAS_PARTY_ROLE D, MAS_PARTY F WHERE A.PARTNER_ID = D.ID AND D.PARTY_ID = F.ID) PARTNER_NAME\n" +
                "          FROM ACC_LEDGER A, ACC_LEDGER_ACCOUNT B\n" +
                "         WHERE A.LEDGER_ACCOUNT_ID = B.ID\n" +
                "           AND B.ID IN ('prepayment_unsettled', 'credit','current_unsettled','current_freeze','invoice_unsettled','invoice_freeze','period')\n" +
                "        ) A, MAS_PARTY_ROLE J, MAS_PARTY Z\n" +
                " WHERE 1 = 1 AND A.PARTNER_ID = J.ID AND J.PARTY_ID = Z.ID AND J.ROLE_ID = 'Customer'\n" +
                "   AND Z.PARTY_TYPE = 'corporate'  and a.company_id='"+param.getString("companyId")+"' and a.partner_id='"+param.getString("partnerId")+"'\n" +
                " GROUP BY PARTNER_ID, PARTNER_NAME, COMPANY, COMPANY_ID, ROLE_ID, PARTY_TYPE\n");
        return toJSONArray(find(sf.toString(), param));
    }
}
